const Moment = require('moment')
const express = require('express')
const fs = require('fs')
const router = express.Router()
const path = require('path')

const expressValidator = require('express-validator')
const check = expressValidator.check
const validationResult = expressValidator.validationResult

const xlsx = require('node-xlsx')
const nodemailer = require('nodemailer')

const UtilsSave = require('../utils/save')

const db = require('../db/db').db

// 创建application/x-www-form-urlencoded
router.use(require('body-parser').urlencoded({
    extended: true
}))
// 创建application/json解析
router.use(require('body-parser').json())

// 报告
router.get('/', (req, res) => {
    let today = Moment().locale('zh-cn').format('YYYY-MM-DD') + ' 00:00:00'
    let tomorrow = Moment().locale('zh-cn').add(1, 'day').format('YYYY-MM-DD') + ' 00:00:00'
    let sqlStr = `
        SELECT a.pro_id, a.type, a.id, a.con, a.duration, a.percent, a.createtime, p.name as projectname, t.name as typename
        FROM task a, project p, tasktype t
        WHERE a.pro_id = p.id AND a.type = t.id AND a.createtime >= '${today}' AND a.createtime < '${tomorrow}';
    `
    let connection = db.connection()
    db.query(connection, sqlStr, (err, result) => {
        try {
            let list = result.map((item) => {
                return Object.assign({}, item, {
                    createtime: Moment(item.createtime).format('YYYY-MM-DD HH:mm:ss'),
                    today: Moment().locale('zh-cn').format('YYYY-MM-DD 周dd')
                })
            })
            let proIds = [...new Set(list.map((item) => item.pro_id))]
            proIds.sort((a, b) => a - b)
            let group = {}
            proIds.forEach((proId) => {
                group[proId] = list.filter((item) => item.pro_id == proId)
            })
            res.render(path.join(path.dirname(__dirname), 'views', 'report', 'index'), {
                title: '报告',
                list: list,
                group: Object.values(group),
                hasSend: req.query.hasSend == 'true'
            })
        } catch (err) { }
    })
})

// 下载
router.get('/download', (req, res) => {
    let today = Moment().locale('zh-cn').format('YYYY-MM-DD') + ' 00:00:00'
    let tomorrow = Moment().locale('zh-cn').add(1, 'day').format('YYYY-MM-DD') + ' 00:00:00'
    let sqlStr = `
        SELECT a.pro_id, a.type, a.id, a.con, a.duration, a.createtime, p.name as projectname, t.name as typename
        FROM task a, project p, tasktype t
        WHERE a.pro_id = p.id AND a.type = t.id AND a.createtime >= '${today}' AND a.createtime < '${tomorrow}'
        ORDER BY a.pro_id ASC;
    `
    let connection = db.connection()
    db.query(connection, sqlStr, (err, result) => {
        try {
            if (result.length == 0) {
                res.render(path.join(path.dirname(__dirname), 'views', 'report', 'index'), {
                    title: '报告',
                    list: [],
                    group: [],
                    err: '未查到今日记录'
                })
            } else {
                let data = result.map((item) => {
                    return Object.assign({}, item, {
                        createtime: Moment(item.createtime).format('YYYY-MM-DD HH:mm:ss')
                    })
                })
                let filename = Moment().locale('zh-cn').format('YYYY-MM-DD') + '.docx'
                let dataPath = "D:/Document/文档-日报记录/" + Moment().locale('zh-cn').format('YYYY') + '/' + Moment().locale('zh-cn').format('MM')
                let report = data.map((item, index) => (index + 1) + '.' + item.typename + '：' + item.projectname + '-' + item.con + ' 100%\r\n')
                report.unshift(Moment().locale('zh-cn').format('YYYY-MM-DD 周dd') + '\r\n')
                report = report.join('')
                async function fn() {
                    await UtilsSave.dirExists(dataPath)
                    fs.writeFile(dataPath + '/' + filename, report, (err) => {
                        if (err) {
                            throw err
                        }
                        return res.redirect('/report')
                    });
                }
                fn()
            }
        } catch (err) { }
    })
})

// 发送日报
router.get('/send', (req, res) => {
    let today = Moment().locale('zh-cn').format('YYYY-MM-DD') + ' 00:00:00'
    let tomorrow = Moment().locale('zh-cn').add(1, 'day').format('YYYY-MM-DD') + ' 00:00:00'
    let sqlStr = `
        SELECT a.pro_id, a.type, a.id, a.con, a.duration, a.percent, a.createtime, p.name as projectname, t.name as typename
        FROM task a, project p, tasktype t
        WHERE a.pro_id = p.id AND a.type = t.id AND a.createtime >= '${today}' AND a.createtime < '${tomorrow}';
    `
    let connection = db.connection()
    db.query(connection, sqlStr, (err, result) => {
        try {
            if (result.length == 0) {
                res.render(path.join(path.dirname(__dirname), 'views', 'report', 'index'), {
                    title: '报告',
                    list: [],
                    group: [],
                    err: '未查到今日记录'
                })
            } else {
                // 读取日报模板
                let reportTemp = 'D:/Document/文档-月度计划/2021/周计划/2021年06月14日周工作计划.xlsx'
                let sheets = xlsx.parse(fs.readFileSync(reportTemp), { cellDates: true })

                // 遍历 sheet，组织 html
                let html = []
                let ths = []
                let colCount = 0
                sheets.forEach((sheet) => {
                    // 遍历xlsx每行内容)
                    for (let rowId in sheet.data) {
                        let row = sheet.data[rowId];
                        if (rowId == 0) {
                            html.push('<tr><td colspan="2"><h3>' + row.join('') + '</h3></td></tr>')
                        }
                        if (rowId == 1) {
                            ths = row
                        }
                        if (row.some((col) => !!col)) {
                            let trBgc = (rowId == 1 ? '#f1f1f1' : '')
                            // 突出显示
                            if (rowId >= 14 && rowId <= 17) {
                                trBgc = '#fac090'
                            }
                            html.push([
                                '<tr style="background-color: ' + trBgc + ';">',
                                    ths.map((th, thIndex) => {
                                        let tdCon = !!row[thIndex] ? row[thIndex] : ''
                                        if (('' + tdCon).indexOf('GMT') != -1) {
                                            tdCon = Moment(tdCon).format('YYYY-MM-DD')
                                        }
                                        // 完成率
                                        if (thIndex == 8) {
                                            !isNaN(tdCon) && (tdCon *= 100) && (tdCon += '%')
                                        }
                                        // 填充选中行的选中列
                                        if (rowId == 15 && thIndex == 7) {
                                            tdCon = Moment().locale('zh-cn').day() - 1
                                            // 更新表格
                                            sheet.data[rowId][thIndex] = tdCon
                                        }
                                        if (rowId == 15 && thIndex == 9) {
                                            tdCon = tdCon.split('\n').map((td) => '<div>' + td + '</div>')
                                            let todayCon = [
                                                '<div>' + Moment().locale('zh-cn').format('YYYY-MM-DD 周dd') + '</div>'
                                            ].concat(result.map((item, index) => {
                                                return '<div>' + (index + 1) + '.' + item.typename + '：' + item.projectname + '-' + item.con + ' ' + item.percent + '%</div>'
                                            }))
                                            tdCon = todayCon.concat(tdCon).join('\n')
                                            // 更新表格
                                            sheet.data[rowId][thIndex] = tdCon.split('\n').map((item) => item.replace('<div>', '').replace('</div>', '')).join('\n')
                                        }
                                        return '<td>' + tdCon + '</td>'
                                    }).join('\n'),
                                '</tr>'
                            ].join('\n'))
                        }
                        colCount = Math.max(row.length, colCount)
                    }
                });

                const smtpTransport = nodemailer.createTransport({
                    host: 'smtp.exmail.qq.com',
                    port: 465,
                    // service: 'qq',
                    // secureConnection: true,
                    secure: true,
                    auth: {
                        user: '1594165162@qq.com',
                        pass: ''
                    }
                })
                let subject = Moment().locale('zh-cn').day(1).format('YYYY年MM月DD日周工作计划')
                let sendHtml = [
                    '<h1>' + subject + '</h1>',
                    '<table border="1" cellpadding="1" cellspacing="0" nowrap="true" style="border-collapse: collapse; table-layout: fixed;">',
                        '<colgroup>',
                            Array.apply(null, { length: colCount }).map((col, index) => {
                                if (index == 1 || index == 2 || index == 9) {
                                    return '<col style="visibility: hidden;" width="300" />'
                                }
                                return '<col style="visibility: hidden;" width="100" />'
                            }).join(''),
                        '</colgroup>',
                        '<tbody>',
                            html.join('\n'),
                        '</tbody>',
                    '</table>'
                ]

                smtpTransport.sendMail({
                    from: '"wp" <1594165162@qq.com>',
                    to: '',
                    cc: '',
                    subject: Moment().locale('zh-cn').format('YYYY年MM月DD日日报'),
                    html: sendHtml.join('\n')
                }, (err, mailRes) => {
                    if (err) {
                        throw err
                    }

                    // 更新文件
                    let buffer = xlsx.build(sheets, {
                        '!cols': [
                            { wpx: 100 },
                            { wpx: 300 },
                            { wpx: 300 },
                            { wpx: 100 },
                            { wpx: 100 },
                            { wpx: 100 },
                            { wpx: 100 },
                            { wpx: 100 },
                            { wpx: 100 },
                            { wpx: 300 }
                        ]
                    })
                    fs.writeFile(reportTemp, buffer, (err) => {
                        if (err) {
                            throw err
                        }
                    })

                    return res.redirect('/report?hasSend=true')
                })
            }
        } catch (err) { }
    })
})

module.exports = router